﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using System.Text;
using Biztalk_Alerts.Models;

namespace Biztalk_Alerts.DBOps
{
    public class BTAlertOperations
    {
        public static string connStrings = null;
        static BTAlertOperations()
        {
            connStrings = System.Configuration.ConfigurationManager.ConnectionStrings["AlertConnection"].ConnectionString;

        }
        public static IList<Applications> getApplications()
        {
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<Applications> apps = null;
            query.Append("select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application");

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                apps = new List<Applications>();

                while (reader.Read())
                {
                    apps.Add(new Applications() { Description = Convert.ToString(reader["nvcDescription"]), nID = Convert.ToString(reader["nID"]), nvcName = Convert.ToString(reader["nvcName"]) });
                }
                conn.Close();
            }

            return apps;
        }



        private static DataTable CreateLocationsSetUpDt(IEnumerable<LocationSetup> locationSetup)
        {
            DataTable table = new DataTable();
            table.Columns.Add("LocationName", typeof(string));
            table.Columns.Add("AppName", typeof(string));            
            table.Columns.Add("restartIfStopped", typeof(bool));
            table.Columns.Add("email", typeof(bool));
            table.Columns.Add("Primary", typeof(string));
            table.Columns.Add("Secondary", typeof(string));
            table.Columns.Add("others", typeof(string));
            table.Columns.Add("mailDuration", typeof(int));
            table.Columns.Add("chkDuration", typeof(int));
            table.Columns.Add("type", typeof(string));

            foreach (LocationSetup location in locationSetup)
            {
                table.Rows.Add(
                    location.ReceiveLocationName, location.AppName, location.restartIfStopped,
                    location.email, location.Primary, location.Secondary, location.others, 
                    location.mailDuration, location.chkDuration, location.type);
            }
            return table;
        }

        public static void InsertLocationSetup(IEnumerable<LocationSetup> locationSetup)
        {
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            
            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("InsertLocationSetup", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@locations", CreateLocationsSetUpDt(locationSetup ));

                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

    }
}